﻿using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;
using C1.Win.C1FlexGrid;
using BHXH_NT.Properties;
using BHXH_NT.Data;

namespace BHXH_NT
{
    public partial class BHYTTREEM : Form
    {
        private string connectionString1 = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString1;
        private string Condition;
        public BHYTTREEM()
        {
            InitializeComponent();
        }
        #region "Method Event"
        private void BHYTTREEM_Load(object sender, System.EventArgs e)
        {
            GetFlexGeneral();
            GetFlexDetail();
        }
        private void btnIn_Click(object sender, System.EventArgs e)
        {
            BHYT dsbhyt = new BHYT();

            for (int i = 1; i < FlxDetail.Rows.Count; i = i + 2)
            {
                if (FlxDetail.Rows[i]["Check"].ToString() == "True")
                {
                    DataRow newRow = dsbhyt.Tables[0].NewRow();
                    newRow["SOKCB1"] = FlxDetail.Rows[i]["SoKCB"];
                    newRow["SOKCB11"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(0, 2);
                    newRow["SOKCB12"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(2, 1);
                    newRow["SOKCB13"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(3, 2);
                    newRow["SOKCB14"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(5, 2);
                    newRow["SOKCB15"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(7, 3);
                    newRow["SOKCB16"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(10);
                    newRow["Ho1"] = FlxDetail.Rows[i]["Ho"];
                    newRow["Ten1"] = FlxDetail.Rows[i]["Ten"];
                    newRow["Hoten1"] = FlxDetail.Rows[i]["Ho"].ToString().Trim() + " " + FlxDetail.Rows[i]["Ten"].ToString().Trim();
                    newRow["TuNgay1"] = FlxDetail.Rows[i]["TuNgay"].ToString().Substring(0, 10);
                    newRow["DenNgay1"] = FlxDetail.Rows[i]["DenNgay"].ToString().Substring(0, 10);
                    newRow["NgaySinh1"] = FlxDetail.Rows[i]["NgaySinh"].ToString().Substring(0, 10);
                    newRow["GioiTinh1"] = FlxDetail.Rows[i]["GioiTinh"];
                    newRow["DiaChi1"] = FlxDetail.Rows[i]["DiaChi"];
                    newRow["Ten_BV1"] = FlxDetail.Rows[i]["Ten_BV"];
                    newRow["Ma_BV1"] = FlxDetail.Rows[i]["Ma_Tinh"] + " - " + FlxDetail.Rows[i]["Ma_BV"];
                    newRow["MaPB1"] = FlxDetail.Rows[i]["MaPB"];
                    newRow["NgayCap1"] = "Ngµy " + FlxDetail.Rows[i]["NgayCap"].ToString().Substring(0, 2) + " th¸ng " + FlxDetail.Rows[i]["NgayCap"].ToString().Substring(3, 2) + " n¨m " + FlxDetail.Rows[i]["NgayCap"].ToString().Substring(6, 4);

                    if (i + 1 < FlxDetail.Rows.Count)
                    {
                        newRow["SOKCB2"] = FlxDetail.Rows[i + 1]["SoKCB"];
                        newRow["SOKCB21"] = FlxDetail.Rows[i + 1]["SoKCB"].ToString().Substring(0, 2);
                        newRow["SOKCB22"] = FlxDetail.Rows[i + 1]["SoKCB"].ToString().Substring(2, 1);
                        newRow["SOKCB23"] = FlxDetail.Rows[i + 1]["SoKCB"].ToString().Substring(3, 2);
                        newRow["SOKCB24"] = FlxDetail.Rows[i + 1]["SoKCB"].ToString().Substring(5, 2);
                        newRow["SOKCB25"] = FlxDetail.Rows[i + 1]["SoKCB"].ToString().Substring(7, 3);
                        newRow["SOKCB26"] = FlxDetail.Rows[i + 1]["SoKCB"].ToString().Substring(10);
                        newRow["Ho2"] = FlxDetail.Rows[i + 1]["Ho"];
                        newRow["Ten2"] = FlxDetail.Rows[i + 1]["Ten"];
                        newRow["Hoten2"] = FlxDetail.Rows[i + 1]["Ho"].ToString().Trim() + " " + FlxDetail.Rows[i + 1]["Ten"].ToString().Trim();
                        newRow["TuNgay2"] = FlxDetail.Rows[i + 1]["TuNgay"].ToString().Substring(0, 10);
                        newRow["DenNgay2"] = FlxDetail.Rows[i + 1]["DenNgay"].ToString().Substring(0, 10);
                        newRow["NgaySinh2"] = FlxDetail.Rows[i + 1]["NgaySinh"].ToString().Substring(0, 10);
                        newRow["GioiTinh2"] = FlxDetail.Rows[i + 1]["GioiTinh"];
                        newRow["DiaChi2"] = FlxDetail.Rows[i + 1]["DiaChi"];
                        newRow["Ten_BV2"] = FlxDetail.Rows[i + 1]["Ten_BV"];
                        newRow["Ma_BV2"] = FlxDetail.Rows[i + 1]["Ma_Tinh"] + " - " + FlxDetail.Rows[i + 1]["Ma_BV"];
                        newRow["MaPB2"] = FlxDetail.Rows[i + 1]["MaPB"];

                        newRow["NgayCap2"] = "Ngµy " + FlxDetail.Rows[i + 1]["NgayCap"].ToString().Substring(0, 2) + " th¸ng " + FlxDetail.Rows[i + 1]["NgayCap"].ToString().Substring(3, 2) + " n¨m " + FlxDetail.Rows[i + 1]["NgayCap"].ToString().Substring(6, 4);
                    }
                    dsbhyt.Tables[0].Rows.Add(newRow);
                }
            }



            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpBHYTTREEM.rpt");
            cr.SetDataSource(dsbhyt);
            f.crpView.ReportSource = cr;
            f.Show();



        }
        #endregion
       
        private void GetFlexGeneral()
        {
           
            string queryString = "exec sp_executesql N'SELECT Inthe_hdr.KEYSL, Inthe_hdr.KEYGH, Inthe_hdr.MADVI,c.TENDVI, Inthe_hdr.NGAY,"
            + "Inthe_hdr.LOAI, Inthe_hdr.TUNGAY, Inthe_hdr.DENNGAY, Inthe_hdr.MA_QUAN,  Inthe_hdr.USERCODE,Inthe_hdr.GHICHU FROM " + Settings.Default.DBName_Sothe + ".dbo.INTHE_HDR Inthe_hdr LEFT JOIN " + Settings.Default.DBName_BHYT + ".Dbo.DMDONVI c ON "
            + "Inthe_hdr.Madvi=c.Madvi WHERE NGAY = @P1 AND Inthe_hdr.MADVI LIKE @P2 Order by Inthe_hdr.MADVI', N'@P1 smalldatetime,@P2 varchar(4)', '" + dtNgay.Value.Year + "/" + dtNgay.Value.Month + "/" + dtNgay.Value.Day + "','%BE%'";

            using (SqlConnection connection =
                   new SqlConnection(connectionString1))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                FlxGeneral.Rows.Count = 1;

                while (reader.Read())
                {
                    FlxGeneral.Rows.Count = FlxGeneral.Rows.Count + 1;
                    FlxGeneral.Rows[i]["STT"] = i;
                    FlxGeneral.Rows[i]["Check"] = 1;
                    FlxGeneral.Rows[i]["MADVI"] = reader["MADVI"];
                    FlxGeneral.Rows[i]["TENDVI"] = reader["TENDVI"];
                    FlxGeneral.Rows[i]["KEYSL"] = reader["KEYSL"];
                    Condition = Condition + "'" + reader["KEYSL"] + "',";
                    i++;
                }
                reader.Close();
                if (Condition != null) { Condition = Condition.Substring(0, Condition.Length - 1); };
            }
        }
        private void GetFlexDetail()
        {
            FlxDetail.Rows.Count = 1;
            if (Condition == null) { return; }

            string queryString = "SELECT 0 As Chon, a.STT, a.Madvi,c.TenDVI,c.MaKhoiKCB as Makhoi,e.Ten_DT,c.Diachi as DCCty,c.dienthoai,c.fax,c.email,"
            + " a.Sobhxh, a.SoKCB, b.Ho, b.Ten, a.TuNgay, a.DenNgay, b.NgaySinh, CASE WHEN b.GioiTinh = 0 THEN 'N÷' else 'Nam' end as GioiTinh, "
            + " b.Namsinh, CASE WHEN c.InTenDvi<>0 THEN c.Tentat ELSE b.DiaChi END As DiaChi,  "
            + " case when a.Ma_Tinh = '' then '" + Properties.Settings.Default.MaTinh + "' else a.Ma_Tinh end as Ma_Tinh, a.Ma_BV,"
            + " case when d.Ten_BV is null then (select Ten_BV from " + Settings.Default.DBName_BHYT + ".Dbo.DMBV as bv where bv.Ma_Tinh = '" + Properties.Settings.Default.MaTinh + "' and bv.Ma_BV = a.Ma_BV) else d.Ten_BV end as Ten_BV,"
            + " a.Tinh_Den, a.NgayMua, a.NgayGiao, a.NgayCap, "
            + " a.LanCap, a.MaDT, a.NgayGiam, a.GhiChu, a.KeySL, a.KeyGH, a.Dain, a.MaPB, "
            + " a.UserCode, a.Ngayin  FROM " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_DTL a "
            + " LEFT JOIN " + Settings.Default.DBName_BHYT + ".Dbo.HOSOCN b ON a.Sobhxh=b.Sobhxh  "
            + " LEFT JOIN " + Settings.Default.DBName_BHYT + ".Dbo.DMDONVI c ON a.Madvi=c.Madvi "
            + " LEFT JOIN " + Settings.Default.DBName_BHYT + ".Dbo.DMBV d on d.Ma_Tinh = a.Ma_Tinh and d.Ma_BV = A.Ma_BV   "
            + " LEFT JOIN " + Settings.Default.DBName_BHYT + ".Dbo.DMKHOIKCB e on c.MaKhoiKCB = e.MA_DT "
            + " WHERE a.KeySL IN (" + Condition + ")  AND (a.Hethan = 0 OR Ngaygiam IS NOT NULL) Order By a.Madvi,a.Sobhxh ";


            using (SqlConnection connection =
                   new SqlConnection(connectionString1))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;


                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                int i = 1;

                while (reader.Read())
                {
                    FlxDetail.Rows.Count = FlxDetail.Rows.Count + 1;
                    FlxDetail.Rows[i]["STT1"] = i;
                    FlxDetail.Rows[i]["Check"] = 1;
                    FlxDetail.Rows[i]["STT"] = reader["STT"];
                    FlxDetail.Rows[i]["Madvi"] = reader["Madvi"];
                    FlxDetail.Rows[i]["Sobhxh"] = reader["Sobhxh"];
                    FlxDetail.Rows[i]["SoKCB"] = reader["SoKCB"];
                    FlxDetail.Rows[i]["Ho"] = reader["Ho"];
                    FlxDetail.Rows[i]["Ten"] = reader["Ten"];
                    FlxDetail.Rows[i]["TuNgay"] = reader["TuNgay"];
                    FlxDetail.Rows[i]["DenNgay"] = reader["DenNgay"];
                    FlxDetail.Rows[i]["NgaySinh"] = reader["NgaySinh"];
                    FlxDetail.Rows[i]["GioiTinh"] = reader["GioiTinh"];
                    FlxDetail.Rows[i]["DiaChi"] = reader["DiaChi"];
                    FlxDetail.Rows[i]["Ten_BV"] = reader["Ten_BV"];
                    FlxDetail.Rows[i]["Ma_BV"] = reader["Ma_BV"];
                    FlxDetail.Rows[i]["Ma_Tinh"] = reader["Ma_Tinh"];
                    FlxDetail.Rows[i]["NgayCap"] = reader["NgayCap"];
                    FlxDetail.Rows[i]["MaPB"] = reader["MaPB"];

                    FlxDetail.Rows[i]["TenDVI"] = reader["TenDVI"];
                    FlxDetail.Rows[i]["Makhoi"] = reader["Makhoi"];
                    FlxDetail.Rows[i]["Ten_DT"] = reader["Ten_DT"];
                    FlxDetail.Rows[i]["DCCty"] = reader["DCCty"];
                    FlxDetail.Rows[i]["dienthoai"] = reader["dienthoai"];
                    FlxDetail.Rows[i]["fax"] = reader["fax"];
                    FlxDetail.Rows[i]["email"] = reader["email"];

                    i++;
                }
                reader.Close();

            }
        }
        private void GetFlexGeneral_Caplai()
        {

            string queryString;
            queryString = "exec sp_executesql N'SELECT distinct Inthe_hdr.MADVI,c.TENDVI FROM " + Settings.Default.DBName_Sothe + ".dbo.INTHE_HDR Inthe_hdr LEFT JOIN " + Settings.Default.DBName_BHYT + ".dbo.DMDONVI c ON "
            + "Inthe_hdr.Madvi=c.Madvi," + Settings.Default.DBName_Sothe + ".dbo.INTHE_CAPLAI d  WHERE d.Madvi = Inthe_hdr.Madvi  AND Inthe_hdr.MADVI LIKE @P2 Order by Inthe_hdr.MADVI', N'@P2 varchar(5)', '%BE%'";
            
            using (SqlConnection connection =
                   new SqlConnection(connectionString1))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;


                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                FlxGeneral.Rows.Count = 1;

                while (reader.Read())
                {
                    FlxGeneral.Rows.Count = FlxGeneral.Rows.Count + 1;
                    FlxGeneral.Rows[i]["STT"] = i;
                    FlxGeneral.Rows[i]["Check"] = 1;
                    FlxGeneral.Rows[i]["MADVI"] = reader["MADVI"];
                    FlxGeneral.Rows[i]["TENDVI"] = reader["TENDVI"];

                    i++;
                }
                reader.Close();
                if (Condition != null) { Condition = Condition.Substring(0, Condition.Length - 1); };
            }
        }
        private void GetFlexDetail_Caplai()
        {
            FlxDetail.Rows.Count = 1;

            string queryString = "select 0 As Chon,a.STT, a.Madvi, a.KeySL,a.KeyP, a.KeyGH, a.Loai,  a.Sobhxh, e.Tencl,"
            + " a.SoKCB, a.TuNgay, a.DenNgay, e.MaCL, a.NgayGiao, a.NgayMua, a.NgayCap, a.LanCap, b.Ho, b.Ten, a.MaTG,"
            + " b.NgaySinh, b.GioiTinh, b.Namsinh, a.Madt, case when b.Ma_Tinh = '' then '" + Properties.Settings.Default.MaTinh + "' else b.Ma_Tinh end as Ma_Tinh , b.Ma_BV, a.Tinh_Den, "
            + " case when f.Ten_BV is null then (select Ten_BV from " + Settings.Default.DBName_BHYT + ".DBO.DMBV as bv where bv.Ma_Tinh = '" + Properties.Settings.Default.MaTinh + "' and bv.Ma_BV = b.Ma_BV) else f.Ten_BV end as Ten_BV,"
            + " CASE WHEN c.InTenDvi<>0 THEN c.Tentat ELSE b.DiaChi END As DiaChi, 0 AS NhanGT, b.MaPB,  a.UserCode, "
            + " a.Ngayin, SPACE(50) As Ghichus FROM " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_DTL a LEFT JOIN " + Settings.Default.DBName_BHYT + ".DBO.HOSOCN b "
            + " ON a.Sobhxh = b.Sobhxh LEFT JOIN " + Settings.Default.DBName_BHYT + ".DBO.DMDONVI c ON a.Madvi = c.Madvi "
            + " INNER JOIN " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_CAPLAI d ON d.SoBHXH = a.SoBHXH and a.Madvi = d.Madvi"
            + " INNER JOIN " + Settings.Default.DBName_Sothe + ".dbo.LYDOCL e on e.MACL = d.MACL "
            + " LEFT JOIN " + Settings.Default.DBName_BHYT + ".DBO.DMBV f on f.Ma_Tinh = b.Ma_Tinh and f.Ma_BV = b.Ma_BV   "
            + " where  KeyGH = (select KEYGH from " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_HDR where KEYSL = (select max(KEYSL) from " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_HDR where Madvi = a.Madvi)) "
            + " AND a.HetHan = 0";


            using (SqlConnection connection =
                   new SqlConnection(connectionString1))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;


                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                FlxDetail.Tree.Column = 4;
                while (reader.Read())
                {
                    if (FlxDetail.Rows[i - 1]["Madvi"].ToString() != reader["Madvi"].ToString())
                    {
                        FlxDetail.Rows.InsertNode(i, 1);
                    }
                    else
                    {
                        FlxDetail.Rows.InsertNode(i, 2);
                    }
                    
                    FlxDetail.SetCellCheck(i, 4, CheckEnum.Checked);
                    FlxDetail.Rows[i]["STT1"] = i;
                    FlxDetail.Rows[i]["Check"] = 1;
                    FlxDetail.Rows[i]["STT"] = reader["STT"];
                    FlxDetail.Rows[i]["Madvi"] = reader["Madvi"];
                    FlxDetail.Rows[i]["Sobhxh"] = reader["Sobhxh"];
                    FlxDetail.Rows[i]["SoKCB"] = reader["SoKCB"];
                    FlxDetail.Rows[i]["Ho"] = reader["Ho"];
                    FlxDetail.Rows[i]["Ten"] = reader["Ten"];
                    FlxDetail.Rows[i]["TuNgay"] = reader["TuNgay"];
                    FlxDetail.Rows[i]["DenNgay"] = reader["DenNgay"];
                    FlxDetail.Rows[i]["NgaySinh"] = reader["NgaySinh"];
                    FlxDetail.Rows[i]["GioiTinh"] = reader["GioiTinh"];
                    FlxDetail.Rows[i]["DiaChi"] = reader["DiaChi"];
                    FlxDetail.Rows[i]["Ma_BV"] = reader["Ma_BV"];
                    FlxDetail.Rows[i]["Ma_Tinh"] = reader["Ma_Tinh"];
                    FlxDetail.Rows[i]["NgayCap"] = reader["NgayCap"];
                    FlxDetail.Rows[i]["Ten_BV"] = reader["Ten_BV"];
                    FlxDetail.Rows[i]["TenDVI"] = reader["LanCap"]; //SO LAN CAP LAI
                    FlxDetail.Rows[i]["Makhoi"] = reader["Tencl"]; //TÊN CAP LAI
                    //FlxDetail.Rows[i]["KEYP"] = reader["KEYP"];
                    FlxDetail.Rows[i]["KEYSL"] = reader["KEYSL"];
                    FlxDetail.Rows[i]["KEYGH"] = reader["KEYGH"];
                    FlxDetail.Rows[i]["MADT"] = reader["MADT"];
                    FlxDetail.Rows[i]["MATG"] = reader["MATG"];
                    FlxDetail.Rows[i]["loai"] = reader["loai"];
                    FlxDetail.Rows[i]["MACL"] = reader["MACL"];

                    //FlxDetail.Rows[i]["Makhoi"] = reader["Makhoi"];
                    //FlxDetail.Rows[i]["Ten_DT"] = reader["Ten_DT"];
                    //FlxDetail.Rows[i]["DCCty"] = reader["DCCty"];
                    //FlxDetail.Rows[i]["dienthoai"] = reader["dienthoai"];
                    //FlxDetail.Rows[i]["fax"] = reader["fax"];
                    //FlxDetail.Rows[i]["email"] = reader["email"];
                    //FlxDetail.Rows[i]["MADVIKCB"] = reader["MADVIKCB"];

                    i++;
                }
                reader.Close();

            }
        }
        private void btnInDanhSach_Click(object sender, System.EventArgs e)
        {
            danhsachthe dsbhyt = new danhsachthe();
            for (int i = 1; i < FlxDetail.Rows.Count; i = i + 1)
            {
                if (FlxDetail.Rows[i]["Check"].ToString() == "True")
                {
                    DataRow newRow = dsbhyt.Tables[0].NewRow();
                    newRow["makhoi"] = FlxDetail.Rows[i]["makhoi"];
                    newRow["Ten_DT"] = FlxDetail.Rows[i]["Ten_DT"];
                    newRow["TenDVI"] = FlxDetail.Rows[i]["TenDVI"];
                    newRow["Madvi"] = FlxDetail.Rows[i]["Madvi"];
                    newRow["DCCty"] = FlxDetail.Rows[i]["DCCty"];
                    newRow["dienthoai"] = FlxDetail.Rows[i]["dienthoai"];
                    newRow["fax"] = FlxDetail.Rows[i]["fax"];
                    newRow["email"] = FlxDetail.Rows[i]["email"];
                    newRow["Sobhxh"] = FlxDetail.Rows[i]["Sobhxh"];
                    newRow["SOKCB"] = FlxDetail.Rows[i]["SoKCB"];
                    newRow["Hoten"] = FlxDetail.Rows[i]["Ho"].ToString().Trim() + " " + FlxDetail.Rows[i]["Ten"].ToString().Trim();
                    newRow["TuNgay"] = FlxDetail.Rows[i]["TuNgay"].ToString().Substring(0, 10);
                    newRow["DenNgay"] = FlxDetail.Rows[i]["DenNgay"].ToString().Substring(0, 10);
                    newRow["NgaySinh"] = FlxDetail.Rows[i]["NgaySinh"];
                    if (FlxDetail.Rows[i]["GioiTinh"].ToString() == "N÷")
                    {
                        newRow["GioiTinh"] = "X";
                    }
                    else
                    {
                        newRow["GioiTinh"] = "";
                    }
                    newRow["DiaChi"] = FlxDetail.Rows[i]["DiaChi"];
                    newRow["Ten_BV"] = FlxDetail.Rows[i]["Ten_BV"];
                    newRow["Ma_BV"] = FlxDetail.Rows[i]["Ma_BV"];

                    dsbhyt.Tables[0].Rows.Add(newRow);
                }

            }

            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpListBHYT.rpt");
            cr.SetDataSource(dsbhyt);
            f.crpView.ReportSource = cr;
            f.Show();

        }
        private void btnSolieu_Click(object sender, System.EventArgs e)
        {
            Condition = null;
            GetFlexGeneral();
            GetFlexDetail();
            //}
            //else
            //{
            //    GetFlexGeneral_Caplai();
            //    GetFlexDetail_Caplai();
            //}
        }
    }
}
